﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.Configuration;

namespace Bing.Toolkit.Data.SQL
{
    /// <summary>
    /// 数据访问对象基类
    /// </summary>
    public class DataAccessObject : IDisposable
    {
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        public DbConnection Connection { get; private set; }
        protected DbProviderFactory ProviderFactory { get; private set; }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connection">数据连接对象</param>
        public DataAccessObject(DbConnection connection) 
        {
            Connection = connection;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="name">数据苦连接配置名称</param>
        public DataAccessObject(string name)
        {
            var setting = ConfigurationManager.ConnectionStrings[name];
            if (setting == null)
            {
                throw new ArgumentException("name", string.Format("Database with name {0} doesn't exist", name));
            }
            ProviderFactory = DbProviderFactories.GetFactory(setting.ProviderName);
            Connection = ProviderFactory.CreateConnection();
            Connection.ConnectionString = setting.ConnectionString;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="providerName">驱动名称</param>
        public DataAccessObject(string connectionString, string providerName)
        {
            ProviderFactory = DbProviderFactories.GetFactory(providerName);
            Connection = ProviderFactory.CreateConnection();
            Connection.ConnectionString = connectionString;
        }
        /// <summary>
        /// 执行一条SQL语句,并返回影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="args">参数</param>
        /// <returns>影响行数</returns>
        public virtual int Execute(string commandText, params object[] args)
        {
            var sql = FormatSql(commandText);
            using (var command = PrepareCommand(sql))
            {
                PrepareCommandParameters(command, args);
                EnsureConnectionOpened();
                int num = command.ExecuteNonQuery();
                return num;
            }
        }
        /// <summary>
        /// 执行一条SQL查询语句，返回一个键值集合对象。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="args">参数</param>
        /// <returns>键值集合对象</returns>
        public virtual List<KeyValueObject> Query(string commandText, params object[] args)
        {
            var sql = FormatSql(commandText);
            using (var command = PrepareCommand(sql))
            {
                PrepareCommandParameters(command, args);
                List<KeyValueObject> datas;
                EnsureConnectionOpened();
                using (DbDataReader dataReader = command.ExecuteReader())
                {
                    datas = KeyValueObject.KeyValueObjectListWithDataReader(dataReader);
                }
                return datas;
            }
        }
        /// <summary>
        /// 执行一条SQL查询语句，并返回一个键值对象
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="args">参数</param>
        /// <returns>键值对象</returns>
        public virtual KeyValueObject Single(string commandText, params object[] args)
        {
            return Query(commandText, args).FirstOrDefault();
        }
        /// <summary>
        /// 执行一条SQL查询语句,并返回一个值
        /// </summary>
        /// <typeparam name="T">值类型</typeparam>
        /// <param name="commandText">SQL语句</param>
        /// <param name="args">参数</param>
        /// <returns>值</returns>
        public virtual T ScalarValue<T>(string commandText, params object[] args)
        {
            var sql = FormatSql(commandText);
            using (var command = PrepareCommand(sql))
            {
                PrepareCommandParameters(command, args);
                EnsureConnectionOpened();
                object result = command.ExecuteScalar();
                return (T)result;
            }
        }
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public virtual int ExecuteNonQuery(string commandText, params DbParameter[] args)
        {
            using (var command = PrepareCommand(commandText))
            {
                if (args.Length > 0)
                    command.Parameters.AddRange(args);
                EnsureConnectionOpened();
                int num = command.ExecuteNonQuery();
                return num;
            }
        }
        /// <summary>
        /// 获取DbDataReader对像
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public virtual DbDataReader ExecuteReader(string commandText, params DbParameter[] args)
        {
            using (var command = PrepareCommand(commandText))
            {
                if (args.Length > 0)
                    command.Parameters.AddRange(args);
                EnsureConnectionOpened();
                return command.ExecuteReader();
            }
        }
        public virtual DataSet ExecuteDataSet(string commandText, params DbParameter[] args)
        {
            using (var command = PrepareCommand(commandText))
            {
                if (args.Length > 0)
                {
                    command.Parameters.AddRange(args);
                }
                DbDataAdapter adapter = ProviderFactory.CreateDataAdapter();
                adapter.SelectCommand = command;
                DataSet dst = new DataSet();
                adapter.Fill(dst);
                return dst;
            }
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <typeparam name="ReturnType">返回值类型，只限基础类型。如:string,int,float...</typeparam>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public virtual ReturnType ExecuteScalar<ReturnType>(string commandText, params DbParameter[] args)
        {
            using (var command = PrepareCommand(commandText))
            {
                if (args.Length > 0)
                    args.ToList().ForEach(o => command.Parameters.Add(o));
                EnsureConnectionOpened();
                object result = command.ExecuteScalar();
                return (ReturnType)result;
            }
        }
        public virtual PageOfList<KeyValueObject> Paging(string commandText, string orderField, int pageIndex, int pageSize, params object[] args)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public virtual void Close()
        {
            Connection.Close();
        }

        #region /* protected Method */

        protected DbCommand PrepareCommand(string commandText)
        {
            DbCommand command = Connection.CreateCommand();
            command.CommandText = commandText;
            return command;
        }

        protected static void PrepareCommandParameters(DbCommand command, object[] args)
        {
            if (args.Length != 0)
            {
                int num = 0;
                foreach (object obj2 in args)
                {
                    DbParameter parameter = command.CreateParameter();
                    parameter.ParameterName = "@p" + num;
                    parameter.Value = args[num++];
                    command.Parameters.Add(parameter);
                }
            }
        }

        protected void EnsureConnectionOpened()
        {
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
        }

        protected string FormatSql(string sqlText)
        {
            return Regex.Replace(sqlText, @"{\d+}", new MatchEvaluator(
                o => String.Format("@p{0}", o.Value.Substring(1, o.Value.Length - 2))));
        }
        #endregion

        #region /* Dispose */
        /// <summary>
        /// 销毁对象
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
        }
        /// <summary>
        /// 销毁对象
        /// </summary>
        /// <param name="disposing">是否为客户端销毁</param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (Connection.State != ConnectionState.Closed)
                {
                    Connection.Close();
                }
                Connection.Dispose();
            }
        }
        /// <summary>
        /// 析构函数
        /// </summary>
        ~DataAccessObject() 
        {
            Dispose(false);
        }
        #endregion

        /// <summary>
        /// 获取一个参数集合
        /// </summary>
        /// <param name="kvObject">键值对象</param>
        /// <returns></returns>
        public virtual DbParameter[] CreateArrayParameter(KeyValueObject kvObject)
        {
            var ps = new DbParameter[kvObject.Count];
            var index = 0;
            foreach (var entry in kvObject.Keys)
            {
                ps[index++] = CreateParameter(entry, kvObject[entry]);
            }
            return ps;
        }
        /// <summary>
        /// 获取一个参数对象
        /// </summary>
        /// <param name="key">键</param>
        /// <param name="val">值</param>
        /// <returns></returns>
        public virtual DbParameter CreateParameter(string key, object val)
        {
            throw new NotImplementedException();
        }
    }
}
